import pandas as pd
import pymysql
import pymysql.cursors
from sklearn.preprocessing import MinMaxScaler  # 修正为正确的归一化类

class MysqlUtils:
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3306,
            charset='utf8'
        )

    def is_holiday(self, date):
        """判断是否为节假日
        Args:
            date (str): 日期字符串 'YYYY-MM-DD'
        Returns:
            int: 1表示节假日，0表示非节假日
        """
        holidays = ['2024-09-03', '2024-10-01', '2024-10-02', 
                   '2024-10-03', '2024-10-04', '2024-10-05']
        return 1 if date in holidays else 0

    def get_hourly_data(self):
        """获取分时段游客数据"""
        cursor = self.conn.cursor()
        sql = """
        SELECT 
            DATE(g.create_time) as date, 
            HOUR(g.create_time) as hour, 
            COUNT(*) as count 
        FROM order_user_gate_rel g 
        WHERE HOUR(g.create_time) BETWEEN 6 AND 23 
        GROUP BY date, hour
        """
        cursor.execute(sql)
        df = pd.DataFrame(cursor.fetchall(), columns=['date', 'hour', 'count'])
        
        # 构建完整时间索引
        date_range = pd.date_range(start='2024-07-01', end='2025-03-02', freq='D')
        hours = range(6, 24)
        full_index = pd.MultiIndex.from_product(
            [date_range, hours],
            names=['date', 'hour']
        )
        df_full = (
            df.set_index(['date', 'hour'])
            .reindex(full_index, fill_value=0)
            .reset_index()
        )
        return df_full

    def get_scenic_data(self):
        """主数据处理方法"""
        df_full = self.get_hourly_data()
        
        # 数据透视（日期为行，小时为列）
        df_pivot = df_full.pivot(index='date', columns='hour', values='count')
        
        # 添加特征列
        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几（0-6）
        df_pivot['month'] = df_pivot.index.month    # 月份
        df_pivot['is_holiday'] = df_pivot.index.strftime('%Y-%m-%d').map(self.is_holiday)
        
        # 特征编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month'], dtype=int)
        
        # 小时数据归一化
        hours_columns = list(range(6, 24))
        df_hours = df_pivot[hours_columns].copy()
        feature_columns = [col for col in df_pivot.columns if col not in hours_columns]
        df_feature = df_pivot[feature_columns].copy()
        
        scaler = MinMaxScaler()  # 修正为正确的归一化类
        scaled_hours = scaler.fit_transform(df_hours)
        df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index)
        
        # 合并结果
        df_final = pd.concat([df_hours_scaled, df_feature], axis=1)
        df_final.to_csv('NW/scenic_data.csv', index=False)
        return df_final

if __name__ == '__main__':
    mu = MysqlUtils()
    data = mu.get_scenic_data()
    print(data.head())